package com.wxtf.platform.main.employee;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.FormatUtil;

/**
 * <li>Title: EmpFamilyAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.employee</li>
 * <li>Description: 员工家庭</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 24, 2012 3:38:25 PM</li>
 *
 * @author chun_chang
 * @version 1.0.0.0
 *
 */
public class EmpFamilyAction extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(EmpFamilyAction.class);

	public EmpFamilyAction(String jsonArray){

	}

	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
			} else if("save".equals(this.getMethodName())) {
				//msg = this.insert(this.getLogicData());
				msg = "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"员工家庭不可以新建\"}";
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if("listChild".equals(this.getMethodName())) {//子女列表
				msg = this.listChild(this.getLogicData());
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg) || "null".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");

		Object[] params = null;
		sql = "from tb_employee s ,tb_family_base t where s.tbe_id = t.tbe_id and s.tbe_employment_forms <> '31'";

		/*
		select s.tbe_id,s.org_id,s.reserve1,s.tbe_idcard,s.tbe_work_permit,s.tbe_name,s.tbe_sex,s.tbe_age,s.tbe_birthday,
		s.tbe_native_palce,s.tbe_family_phone,s.tbe_address,
		s.tbe_marital_status,s.tbe_marital_condition,
		s.tbe_is_again_birth,s.tbe_is_barrenness,s.tbe_is_late_marriage,s.tbe_is_late_birth,s.tbe_status,
		t.tbfi_id,t.tbe_spouse_id,t.tbfi_spouse_name,t.tbfi_spouse_sex,t.tbfi_spouse_age,t.tbfi_spouse_birthday,
		t.tbfi_spouse_idcard,t.tbfi_spouse_department,t.tbfi_spouse_position,t.tbfi_spouse_is_join,
		t.tbfi_phone,t.tbfi_mobile,t.tbfi_address,t.tbfi_peo_num,t.tbfi_children_num,t.tbfi_children_mnum,t.tbfi_children_fnum,t.tbfi_memo,
		t.reserve1,t.reserve2,t.reserve3,t.reserve4,t.reserve5,t.create_by,t.create_time,t.last_modify_by,t.last_modify_time
		from tb_employee s LEFT JOIN tb_family_base t on s.tbe_id = t.tbe_id 
		and s.org_id = ?
		 */
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			
			if("init".equals(types)) {// 初始查询
				sql += " and s.org_id in (" + user.getCurSubOrgId() + ")";
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String forms = jsonObject.getString("tbe_employment_forms");//用工形式
				String tbe_name = jsonObject.getString("tbe_name");//姓名
				String tbe_idcard = jsonObject.getString("tbe_idcard");//身份证
				String tbfi_spouse_name = jsonObject.getString("tbfi_spouse_name");//配偶姓名
				String tbfi_spouse_idcard = jsonObject.getString("tbfi_spouse_idcard");//配偶身份证
				
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and s.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				if(StringUtils.isNotBlank(forms)){
					if(!"0".equals(forms)){//全部
						buff.append(" and s.tbe_employment_forms = '").append(forms).append("'");
					}
				}
				if(StringUtils.isNotBlank(tbe_name)){
					buff.append(" and s.tbe_name like '%").append(tbe_name).append("%'");
				}
				if(StringUtils.isNotBlank(tbe_idcard)){
					buff.append(" and s.tbe_idcard like '%").append(tbe_idcard).append("%'");
				}
				if(StringUtils.isNotBlank(tbfi_spouse_name)){
					buff.append(" and t.tbfi_spouse_name like '%").append(tbfi_spouse_name).append("%'");
				}
				if(StringUtils.isNotBlank(tbfi_spouse_idcard)){
					buff.append(" and t.tbfi_spouse_idcard like '%").append(tbfi_spouse_idcard).append("%'");
				}
				sql += buff.toString();
			}else if("serch2".equals(types)) {// 高级查询
				String where = jsonObject.getString("where");//机构ID串
				sql += where;
			}
		}
		
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			if("org_name".equals(sortname)){
				sql += " order by s.reserve1 " + sortorder;
			} else {
				sql += " order by s." + sortname + " " + sortorder;
			}
		}
		
		String countSql = "select count(*) " + sql;
		String querySql = "select s.tbe_id,s.org_id,s.reserve1 as org_name,s.tbe_spell_code,s.tbe_idcard,s.tbe_work_permit,s.tbe_name,s.tbe_sex,s.tbe_age,s.tbe_birthday,s.tbe_position,s.tbe_native_palce,s.tbe_family_phone,s.tbe_address,s.tbe_marital_status,s.tbe_marital_condition,s.tbe_is_again_birth,s.tbe_is_barrenness,s.tbe_is_late_marriage,s.tbe_is_late_birth,s.tbe_status,t.tbfi_id,t.tbe_spouse_id,t.tbfi_spouse_name,t.tbfi_spouse_sex,t.tbfi_spouse_age,t.tbfi_spouse_birthday,t.tbfi_spouse_idcard,t.tbfi_spouse_department,t.tbfi_spouse_position,t.tbfi_spouse_is_join,t.tbfi_phone,t.tbfi_mobile,t.tbfi_address,t.tbfi_peo_num,t.tbfi_children_num,t.tbfi_children_mnum,t.tbfi_children_fnum,t.tbfi_memo,t.tbfi_valid,t.reserve1,t.reserve2,t.reserve3,t.reserve4,t.reserve5,t.create_by,t.create_time,t.last_modify_by,t.last_modify_time " + sql;
		String[] fields = { "tbe_id", "org_id", "org_name", "tbe_spell_code","tbe_idcard", "tbe_work_permit", "tbe_name", "tbe_sex",
				"tbe_age", "tbe_birthday","tbe_position", "tbe_native_palce", "tbe_family_phone", "tbe_address", "tbe_marital_status",
				"tbe_marital_condition", "tbe_is_again_birth", "tbe_is_barrenness", "tbe_is_late_marriage",
				"tbe_is_late_birth", "tbe_status", "tbfi_id", "tbe_spouse_id", "tbfi_spouse_name", "tbfi_spouse_sex",
				"tbfi_spouse_age", "tbfi_spouse_birthday", "tbfi_spouse_idcard", "tbfi_spouse_department",
				"tbfi_spouse_position", "tbfi_spouse_is_join", "tbfi_phone", "tbfi_mobile", "tbfi_address",
				"tbfi_peo_num", "tbfi_children_num", "tbfi_children_mnum", "tbfi_children_fnum", "tbfi_memo","tbfi_valid",
				"reserve1", "reserve2", "reserve3", "reserve4", "reserve5", "create_by", "create_time",
				"last_modify_by", "last_modify_time" };
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), params);
	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		String[] fields = {
			"tbfi_id", "org_id", "tbe_id", "tbe_spouse_id", "tbfi_spouse_name", "tbfi_spouse_sex",
			"tbfi_spouse_age", "tbfi_spouse_birthday", "tbfi_spouse_idcard", "tbfi_spouse_department",
			"tbfi_spouse_position", "tbfi_spouse_is_join", "tbfi_phone", "tbfi_mobile", "tbfi_address",
			"tbfi_peo_num", "tbfi_children_num", "tbfi_children_mnum", "tbfi_children_fnum", "tbfi_valid", "tbfi_memo",
			"reserve1", "reserve2", "reserve3", "reserve4", "reserve5", "create_by", "create_time", "last_modify_by", "last_modify_time"
		};
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		
		sql = "select count(*) from tb_family_base where tbe_id = ?";
		int cnt = AccessDataBaseUtil.queryForInt(sql, new Object[]{ jsonObject.getString("tbe_id") });
		if(cnt >= 1){
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"员工已经存在家庭，不可以再次建立\"}";
		}
		
		StringBuffer buff = new StringBuffer();
		buff.append("insert into tb_family_base(tbfi_id");
		Object[] obj = new Object[fields.length];
		for (int j = 1; j < fields.length; j++) {
			obj[j] = jsonObject.getString(fields[j]);
			buff.append(",").append(fields[j]);
		}
		buff.append(") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		obj[0] = AccessDataBaseUtil.getPKUUID();
		
		String curTime = FormatUtil.getCurentDateTime();
		obj[26] = user.getUserId();
		obj[27] = curTime;
		obj[28] = user.getUserId();
		obj[29] = curTime;
		
		AccessDataBaseUtil.executeSQLUpdate(buff.toString(), obj);
		return "{\"result\":true,\"resultmessage\":\"\",\"resultrecord\":\"保存成功\"}";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		
		//根据身份证号判断选择的配偶是否已经被其他人选择
		String tbe_id = jsonObject.getString("tbe_id");
		String tbfi_spouse_is_join = jsonObject.getString("tbfi_spouse_is_join");
		if("1".equals(tbfi_spouse_is_join)){//配偶是一汽员工
			String tbfi_spouse_idcard = jsonObject.getString("tbfi_spouse_idcard");
			String tbe_spouse_id = jsonObject.getString("tbe_spouse_id");
			sql = "select count(*) from tb_family_base where tbe_spouse_id = ? and tbe_id <> ?";
			int cnt = AccessDataBaseUtil.queryForInt(sql, new Object[]{tbe_spouse_id, tbe_id });
			if(cnt > 0){
				return "\"配偶已经被选择过\"";
			}
			sql = "select count(*) from tb_family_base where tbe_id = ? and tbfi_spouse_idcard is not null and tbfi_spouse_idcard <> ?";
			cnt = AccessDataBaseUtil.queryForInt(sql, new Object[]{ tbe_spouse_id, tbfi_spouse_idcard });
			if(cnt > 1){
				return "\"配偶已经被选择过\"";
			}
		}
		
		
		StringBuffer buff = new StringBuffer();
		buff.append("update tb_family_base set tbfi_spouse_name='").append(jsonObject.getString("tbfi_spouse_name"))
			.append("',tbe_spouse_id='").append(jsonObject.getString("tbe_spouse_id"))
			.append("',tbfi_spouse_sex='").append(jsonObject.getString("tbfi_spouse_sex"))
			.append("',tbfi_spouse_age=").append(StringUtils.isBlank(jsonObject.getString("tbfi_spouse_age"))? 0 : jsonObject.getString("tbfi_spouse_age"))
			.append(",tbfi_spouse_birthday='").append(jsonObject.getString("tbfi_spouse_birthday"))
			.append("',tbfi_spouse_idcard='").append(jsonObject.getString("tbfi_spouse_idcard"))
			.append("',tbfi_spouse_department='").append(jsonObject.getString("tbfi_spouse_department"))
			.append("',tbfi_spouse_position='").append(jsonObject.getString("tbfi_spouse_position"))
			.append("',tbfi_spouse_is_join='").append(jsonObject.getString("tbfi_spouse_is_join"))
			.append("',tbfi_phone='").append(jsonObject.getString("tbfi_phone"))
			.append("',tbfi_mobile='").append(jsonObject.getString("tbfi_mobile"))
			.append("',tbfi_address='").append(jsonObject.getString("tbfi_address"))
			.append("',tbfi_peo_num=").append(StringUtils.isBlank(jsonObject.getString("tbfi_peo_num"))? 0 : jsonObject.getString("tbfi_peo_num"))
			.append(",tbfi_children_num=").append(StringUtils.isBlank(jsonObject.getString("tbfi_children_num"))? 0 : jsonObject.getString("tbfi_children_num"))
			.append(",tbfi_children_mnum=").append(StringUtils.isBlank(jsonObject.getString("tbfi_children_mnum"))? 0 : jsonObject.getString("tbfi_children_mnum"))
			.append(",tbfi_children_fnum=").append(StringUtils.isBlank(jsonObject.getString("tbfi_children_fnum"))? 0 : jsonObject.getString("tbfi_children_fnum"))
			.append(",tbfi_memo='").append(jsonObject.getString("tbfi_memo"))
			.append("',reserve1='").append(jsonObject.getString("reserve1"))
			.append("',reserve2='").append(jsonObject.getString("reserve2"))
			.append("',reserve3='").append(jsonObject.getString("reserve3"))
			.append("',reserve4='").append(jsonObject.getString("reserve4"))
			.append("',reserve5='").append(jsonObject.getString("reserve5"))
			.append("',last_modify_by='").append(user.getUserId())
			.append("',last_modify_time='").append(curTime)
			.append("' where tbe_id ='").append(jsonObject.getString("tbe_id")).append("'");
		
		StringBuffer buff_tfc = new StringBuffer();
		//更改配偶员工表中信息
		buff_tfc.append("update tb_employee set tbe_marital_status='8A90F0D0176C4961B322BEB26703A321'")
				.append(" where tbe_idcard='").append(jsonObject.getString("tbfi_spouse_idcard")).append("'");
		StringBuffer buff_tfs = new StringBuffer();
		// 更改配偶家庭表中信息
		buff_tfs.append("update tb_family_base set tbfi_valid='0'").append(",tbe_spouse_id='").append(jsonObject.getString("tbe_id"))
			.append("',tbfi_spouse_name='").append(jsonObject.getString("tbe_name"))
			.append("',tbfi_spouse_sex='").append(jsonObject.getString("tbe_sex"))
			.append("',tbfi_spouse_birthday='").append(jsonObject.getString("tbe_birthday"))
			.append("',tbfi_spouse_idcard='").append(jsonObject.getString("tbe_idcard"))
			.append("',tbfi_spouse_department='").append(jsonObject.getString("org_name"))
			.append("',tbfi_spouse_position='").append(jsonObject.getString("tbe_spell_code"))
			.append("',tbfi_spouse_is_join='").append(jsonObject.getString("tbfi_spouse_is_join"))
			.append("',last_modify_by='").append(user.getUserId())
			.append("',last_modify_time='").append(curTime)
			.append("',tbfi_peo_num=").append(StringUtils.isBlank(jsonObject.getString("tbfi_peo_num"))? 0 : jsonObject.getString("tbfi_peo_num"))
			.append(",tbfi_children_num=").append(StringUtils.isBlank(jsonObject.getString("tbfi_children_num"))? 0 : jsonObject.getString("tbfi_children_num"))
			.append(",tbfi_children_mnum=").append(StringUtils.isBlank(jsonObject.getString("tbfi_children_mnum"))? 0 : jsonObject.getString("tbfi_children_mnum"))
			.append(",tbfi_children_fnum=").append(StringUtils.isBlank(jsonObject.getString("tbfi_children_fnum"))? 0 : jsonObject.getString("tbfi_children_fnum"))
			.append(" where tbe_id=(")
			.append("select tbe_id from tb_employee where tbe_idcard='")
			.append(jsonObject.getString("tbfi_spouse_idcard")).append("')");
		//更改员工本人的婚姻信息
		StringBuffer buff_be=new StringBuffer();
		buff_be.append("update tb_employee set tbe_marital_status='").append(jsonObject.getString("tbe_marital_status"))
			.append("',last_modify_by='").append(user.getUserId()).append("',last_modify_time='").append(curTime)
	        .append("' where tbe_id='").append(jsonObject.getString("tbe_id")).append("'");
		
		String[] array_sql = { buff.toString(), buff_tfc.toString(), buff_tfs.toString(), buff_be.toString() };
		
		AccessDataBaseUtil.executeSQLUpdate(array_sql);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String tbe_id = jsonObject.getString("tbe_id");

		sql = "update tb_family_base set tbe_spouse_id = '',tbfi_spouse_name = '',tbfi_spouse_sex = '',tbfi_spouse_age = '',tbfi_spouse_birthday = '',tbfi_spouse_idcard = '',tbfi_spouse_department = '',tbfi_spouse_position = '',tbfi_spouse_is_join = '0' where tbe_id = ?";
		AccessDataBaseUtil.executeSQLUpdate(sql, new Object[] { tbe_id });
		return "\"删除成功\"";
	}

	public Object listChild(Object jsonArray) throws Exception {
		
		return null;
	}

	

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}

}
